﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using SARS.DAO.Facades;
using SARS.Entity;

namespace SARS.DAO
{
    public class CourseDAO : ICourseDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public List<CourseEntity> RetriveAllCourse()
        {
            var courseEntityList = new List<CourseEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[Courses]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var courseEntity = new CourseEntity
                    {
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        CourseName = _dr["CourseName"].ToString(),
                        CourseType = _dr["CourseType"].ToString(),
                        StartDate = DateTime.Parse(_dr["StartDate"].ToString()),
                        EndDate = DateTime.Parse(_dr["EndDate"].ToString()),
                        Status = Int32.Parse(_dr["Status"].ToString()),
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        CoordinatorID = Int32.Parse(_dr["CoordinatorID"].ToString())
                    };

                    courseEntityList.Add(courseEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return courseEntityList;
        }

        public CourseEntity RetrieveCourseById(int courseId)
        {
            var courseEntity = new CourseEntity();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [dbo].[Courses] WHERE [CourseID] = @courseID ORDER BY [StartDate],[EndDate]",
                        _conn);
                _cmd.Parameters.Add("@courseID", SqlDbType.Int);
                _cmd.Parameters["@courseID"].Value = courseId;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    courseEntity.CourseID = Int32.Parse(_dr["CourseID"].ToString());
                    courseEntity.CourseName = _dr["CourseName"].ToString();
                    courseEntity.CourseType = _dr["CourseType"].ToString();
                    courseEntity.StartDate = DateTime.Parse(_dr["StartDate"].ToString());
                    courseEntity.EndDate = DateTime.Parse(_dr["EndDate"].ToString());
                    courseEntity.Status = Int32.Parse(_dr["Status"].ToString());
                    courseEntity.ProgramID = Int32.Parse(_dr["ProgramID"].ToString());
                    courseEntity.CoordinatorID = Int32.Parse(_dr["CoordinatorID"].ToString());
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return courseEntity;
        }

        public Int32 AssignStudentToCourses(List<StudentCourseEntity> studentCourseEntities)
        {
            int result = 0;

            try
            {
                _conn.Open();

                foreach (StudentCourseEntity studentCourseEntity in studentCourseEntities)
                {
                    _cmd =
                        new SqlCommand(
                            "INSERT INTO [SAR].[dbo].[StudentCourse]([StudentID],[CourseID],[RegisteredDate],[Status]) VALUES(@StudentID,@CourseID,@RegisteredDate,@Status)",
                            _conn);

                    _cmd.Parameters.Add("@StudentID", SqlDbType.Int);
                    _cmd.Parameters["@StudentID"].Value = studentCourseEntity.StudentID;

                    _cmd.Parameters.Add("@CourseID", SqlDbType.Int);
                    _cmd.Parameters["@CourseID"].Value = studentCourseEntity.CourseID;

                    _cmd.Parameters.Add("@RegisteredDate", SqlDbType.DateTime);
                    _cmd.Parameters["@RegisteredDate"].Value = studentCourseEntity.RegisteredDate;

                    _cmd.Parameters.Add("@Status", SqlDbType.Int);
                    _cmd.Parameters["@Status"].Value = studentCourseEntity.Status;

                    result += _cmd.ExecuteNonQuery();
                }

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public List<CourseEntity> RetriveCoursesByProgrammeID(int programmeId)
        {
            var courseEntityList = new List<CourseEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT c.[CourseID]"
                                      + " ,c.[CourseName]"
                                      + " ,c.[CourseType]"
                                      + " ,c.[StartDate]"
                                      + " ,c.[EndDate]"
                                      + " ,c.[Status]"
                                      + " ,c.[ProgramID]"
                                      + " ,c.[CoordinatorID]"
                                      + " ,u.[FirstName]"
                                      + " ,u.[LastName]"
                                      + " FROM [dbo].[Courses] c"
                                      + " JOIN [dbo].[Users] u ON c.[CoordinatorID] = u.[UserID]"
                                      + " WHERE c.[ProgramID] = @ProgrammeID", _conn);

                _cmd.Parameters.Add("@ProgrammeID", SqlDbType.Int);
                _cmd.Parameters["@ProgrammeID"].Value = programmeId;

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var coordinator = new CoordinatorEntity
                    {
                        UserID = Int32.Parse(_dr["CoordinatorID"].ToString()),
                        FirstName = _dr["FirstName"].ToString(),
                        LastName = _dr["LastName"].ToString()
                    };

                    var courseEntity = new CourseEntity
                    {
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        CourseName = _dr["CourseName"].ToString(),
                        CourseType = _dr["CourseType"].ToString(),
                        StartDate = DateTime.Parse(_dr["StartDate"].ToString()),
                        EndDate = DateTime.Parse(_dr["EndDate"].ToString()),
                        Status = Int32.Parse(_dr["Status"].ToString()),
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        CoordinatorID = Int32.Parse(_dr["CoordinatorID"].ToString()),
                        Coordinator = coordinator
                    };

                    courseEntityList.Add(courseEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return courseEntityList;
        }

        public List<StudentCourseEntity> RetrieveStudentCourse()
        {
            var studentCourseEntityList = new List<StudentCourseEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[StudentCourse]", _conn);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var studentCourseEntity = new StudentCourseEntity
                    {
                        StudentID = Int32.Parse(_dr["StudentID"].ToString()),
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        RegisteredDate = DateTime.Parse(_dr["RegisteredDate"].ToString()),
                        ModifiedDate =
                            _dr["ModifiedDate"] != DBNull.Value
                                ? DateTime.Parse(_dr["ModifiedDate"].ToString())
                                : (DateTime?) null,
                        Status = (CourseRegistryStatus) Int32.Parse(_dr["Status"].ToString())
                    };

                    studentCourseEntityList.Add(studentCourseEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return studentCourseEntityList;
        }

        public int DeleteCourseById(int id)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("DELETE FROM [dbo].[Courses] WHERE [CourseID] = @CourseID", _conn);

                _cmd.Parameters.AddWithValue("@CourseID", id);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public int UpdateCourse(CourseEntity entity)
        {
            throw new NotImplementedException();
        }

        public List<StudentCourseEntity> RetrieveStudentCourseByCourseId(int id)
        {
            var studentCourseEntityList = new List<StudentCourseEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[StudentCourse] WHERE [CourseID] = @CourseID", _conn);
                _cmd.Parameters.AddWithValue("@CourseID", id);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var studentCourseEntity = new StudentCourseEntity
                    {
                        StudentID = Int32.Parse(_dr["StudentID"].ToString()),
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        RegisteredDate = DateTime.Parse(_dr["RegisteredDate"].ToString()),
                        ModifiedDate =
                            _dr["ModifiedDate"] != DBNull.Value
                                ? DateTime.Parse(_dr["ModifiedDate"].ToString())
                                : (DateTime?) null,
                        Status = (CourseRegistryStatus) Int32.Parse(_dr["Status"].ToString())
                    };

                    studentCourseEntityList.Add(studentCourseEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return studentCourseEntityList;
        }

        public int DeleteCourseStudentById(int courseId, int studentId)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "DELETE FROM [dbo].[StudentCourse] WHERE [CourseID] = @CourseID AND [StudentID] = @StudentID",
                        _conn);

                _cmd.Parameters.AddWithValue("@CourseID", courseId);
                _cmd.Parameters.AddWithValue("@StudentID", studentId);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public int UpdateStudentCourseById(StudentCourseEntity entity)
        {
            int result = 0;

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("UPDATE [dbo].[StudentCourse]"
                                      + " SET [ModifiedDate] = @ModifiedDate,"
                                      + " [Status] = @Status"
                                      + " WHERE [StudentID] = @StudentID AND [CourseID] = @CourseID", _conn);

                _cmd.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
                _cmd.Parameters.AddWithValue("@StudentID", entity.StudentID);
                _cmd.Parameters.AddWithValue("@CourseID", entity.CourseID);
                _cmd.Parameters.AddWithValue("@Status", (int) entity.Status);

                result = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public List<CourseEntity> GetAllCourseByStudentIdAndProgramId(int programId, int studentId)
        {
            var courseEntityList = new List<CourseEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("Select * from [Courses] AS C Join [StudentCourse] AS SC " +
                                      "ON C.CourseID = SC.CourseID WHERE C.ProgramID =@programId AND SC.StudentID =@stdId",
                    _conn);
                _cmd.Parameters.Add(new SqlParameter("@programId", programId));
                _cmd.Parameters.Add(new SqlParameter("@stdId", studentId));

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var courseEntity = new CourseEntity
                    {
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        CourseName = _dr["CourseName"].ToString(),
                        CourseType = _dr["CourseType"].ToString(),
                        StartDate = DateTime.Parse(_dr["StartDate"].ToString()),
                        EndDate = DateTime.Parse(_dr["EndDate"].ToString()),
                        Status = Int32.Parse(_dr["Status"].ToString()),
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        CoordinatorID = Int32.Parse(_dr["CoordinatorID"].ToString())
                    };

                    courseEntityList.Add(courseEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return courseEntityList;
        }


        public List<CourseEntity> GetAllCourseByTutorIdAndProgramId(int programId, int tutorId)
        {
            var courseEntityList = new List<CourseEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("Select * From [Courses] WHERE [ProgramID] = @programId AND [CourseID] IN " +
                                      "(Select DISTINCT CourseID FROM [Sessions] WHERE [ManagerID] = @tutorId)", _conn);
                _cmd.Parameters.Add(new SqlParameter("@programId", programId));
                _cmd.Parameters.Add(new SqlParameter("@tutorId", tutorId));

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var courseEntity = new CourseEntity
                    {
                        CourseID = Int32.Parse(_dr["CourseID"].ToString()),
                        CourseName = _dr["CourseName"].ToString(),
                        CourseType = _dr["CourseType"].ToString(),
                        StartDate = DateTime.Parse(_dr["StartDate"].ToString()),
                        EndDate = DateTime.Parse(_dr["EndDate"].ToString()),
                        Status = Int32.Parse(_dr["Status"].ToString()),
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        CoordinatorID = Int32.Parse(_dr["CoordinatorID"].ToString())
                    };

                    courseEntityList.Add(courseEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Course] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return courseEntityList;
        }
    }
}